﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Data;
using Sunny.UI;
namespace project
{
    class SqlHelper
    {
        private static string admin_id = "nwpu";
        private static string adming_pwd = "nwpuDB2021";
        private static string connetStr = "server=localhost;user=" + admin_id + ";pwd="
                + adming_pwd + ";database=libary_management";


        /// <summary>
        /// 执行增、删、改方法，返回受影响的行数
        /// </summary>
        /// <param name="sql">要执行增、删、改的sql语句</param>
        /// <returns>返回受影响的行数</returns>
        public static int ExecuteNonQuery(string sql)
        {
            try 
            {
                MySqlConnection conn = new MySqlConnection(connetStr);
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(sql, conn);

                return cmd.ExecuteNonQuery();
            }
            catch(Exception e)
            {
                MessageBox.Show(e.Message,"提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return 0;
                   
            }
        }
        public static bool is_admin_pwd_ok(string id, string pwd)
        {

            try
            {
                MySqlConnection conn = new MySqlConnection(connetStr);
                conn.Open();

                string sql1 = "select * from admin_info where admin_number=(@id) and admin_pwd=(@pwd);";
                MySqlCommand cmd = new MySqlCommand(sql1, conn);
                cmd.Parameters.Add(new MySqlParameter("@id", id));
                cmd.Parameters.Add(new MySqlParameter("@pwd", pwd));
                MySqlDataReader reader = cmd.ExecuteReader();
                Console.WriteLine("read is {0}", reader.HasRows);
                if (reader.HasRows)
                {
                    return true;
                }
                else { return false; }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;

            }

        }
        public static bool is_user_pwd_ok(string id, string pwd)
        {

            try
            {
                MySqlConnection conn = new MySqlConnection(connetStr);
                conn.Open();

                string sql1 = "select * from student where number=(@id) and pwd=(@pwd);";
                MySqlCommand cmd = new MySqlCommand(sql1, conn);
                cmd.Parameters.Add(new MySqlParameter("@id", id));
                cmd.Parameters.Add(new MySqlParameter("@pwd", pwd));
                MySqlDataReader reader = cmd.ExecuteReader();
                Console.WriteLine("read is {0}", reader.HasRows);
                if (reader.HasRows)
                {
                    return true;
                }
                else { return false; }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;

            }

        }
                /// <summary>
        /// 返回第一行第一列数据的查询方法，一般为查询结果的条数
        /// </summary>
        /// <param name="sql">要执行查询的sql语句</param>
        /// <returns>返回查询结果的数量</returns>
        public static object ExecuteScalar(string sql)
        {
            try
            {
                 MySqlConnection conn = new MySqlConnection(connetStr);
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(sql, conn);

                return cmd.ExecuteScalar();
            }
            catch(Exception e)
            {

                MessageBox.Show(e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }
        }

        /// <summary>
        /// 返回一个DataSet的查询方法
        /// </summary>
        /// <param name="sql">要执行查询的sql语句</param>
        /// <returns>返回一个DataSet结果集</returns>
        public static DataSet ExcuteDataSet(string sql)
        {
            using (MySqlConnection conn = new MySqlConnection(connetStr))
            {
                conn.Open();
                // 表示用于填充 DataSet 和更新 SQL Server 数据库的一组数据命令和一个数据库连接
                MySqlDataAdapter sda = new MySqlDataAdapter(sql, conn);
                //表示数据在内存中的缓存
                DataSet ds = new DataSet();
                sda.Fill(ds);

                return ds;
            }
        }
    public static object rent_books(string stu_num,string book_num)
        {

            try
            {
                MySqlConnection conn = new MySqlConnection(connetStr);
                conn.Open();

                MySqlCommand cmd = new MySqlCommand("rent_book", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new MySqlParameter("stu_num", MySqlDbType.VarChar));
                cmd.Parameters.Add(new MySqlParameter("book_num", MySqlDbType.VarChar));
                //把具体的值传给输入参数
                cmd.Parameters["stu_num"].Value = stu_num;
                cmd.Parameters["book_num"].Value = book_num;
                //如cmd.Parameters["@riqi"].Value = this.textBox1.Text;
                //执行存储过程
                return  cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;

            }

        }
    public static object return_book(string stu_num,string book_num,string indate)
        {

            try
            {
                MySqlConnection conn = new MySqlConnection(connetStr);
                conn.Open();

                MySqlCommand cmd = new MySqlCommand("return_book", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new MySqlParameter("stu_num", MySqlDbType.VarChar));
                cmd.Parameters.Add(new MySqlParameter("book_num", MySqlDbType.VarChar));
                cmd.Parameters.Add(new MySqlParameter("indate", MySqlDbType.DateTime));
                //把具体的值传给输入参数
                cmd.Parameters["stu_num"].Value = stu_num;
                cmd.Parameters["book_num"].Value = book_num;
                cmd.Parameters["indate"].Value = indate;
                //如cmd.Parameters["@riqi"].Value = this.textBox1.Text;
                //执行存储过程
                return  cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;

            }

        }
        public static MySqlDataReader ExcuteReader(string sqlstr)
        {
            try
            {
                
                MySqlConnection conn = new MySqlConnection(connetStr);
                conn.Open();
                MySqlCommand cmd_select_spj = new MySqlCommand(sqlstr, conn);
                MySqlDataReader rdr = cmd_select_spj.ExecuteReader();
                return rdr;
            }
            catch (Exception e)
            {

                MessageBox.Show(e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return null;
            }
        }
    }
}


//namespace myCsharptest
//{
//    class Program
//    {
//        static void Main(string[] args)
//        {
//            string conString = "server=localhost;uid=root;pwd=Yang2001;database=spj_mng";
//            MySqlConnection connect = null;
//            try
//            {
//                connect = new MySqlConnection(conString);
//                connect.Open();
//                Console.WriteLine(String.Format("Connect successfully"));

//                //插入
//                //string sql_insert_s = "insert into s values('S6','华为',50,'西安')";
//                //MySqlCommand cmd_insert_s = new MySqlCommand(sql_insert_s, connect);
//                //cmd_insert_s.ExecuteNonQuery();
//                // 修改
//                //string sql_update_p = "update p set color = '黄' where color='红' ";
//                //MySqlCommand cmd_update_p = new MySqlCommand(sql_update_p, connect);
//                //cmd_update_p.ExecuteNonQuery();
//                // 删除
//                //string sql_delete_j = "delete from j where jno='J7'";
//                //MySqlCommand cmd_delete_j = new MySqlCommand(sql_delete_j, connect);
//                //cmd_delete_j.ExecuteNonQuery();
//                // 查询
//                string sql_select_spj = "select * from spj";
//                MySqlCommand cmd_select_spj = new MySqlCommand(sql_select_spj, connect);
//                MySqlDataReader rdr = cmd_select_spj.ExecuteReader();
//                Console.WriteLine("in table spj:");
//                Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}",
//                            rdr.GetName(0), rdr.GetName(1), rdr.GetName(2), rdr.GetName(3),rdr.GetName(4));
//                while (rdr.Read())
//                {
//                    Console.WriteLine("{0}\t{1}\t{2}\t{3},\t{4}",
//                                rdr.GetString(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3),rdr.GetString(4));
//                }
//            }
//            catch (Exception e)
//            {

//                Console.WriteLine(e.Message);
//            }
//            finally
//            {
//                Console.ReadLine();
//                connect.Close();
//            }
//        }
//    }
//}

